package cn.ffcs.unm.staff.dao.impl;

import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang.StringEscapeUtils;
import org.springframework.stereotype.Repository;

import cn.ffcs.unm.staff.dao.StaffNeimBaseInfoDao;
import cn.ffcs.unm.staff.model.CustomFile;
import cn.ffcs.unm.staff.model.FormerSituation;
import cn.ffcs.unm.staff.model.HistoryRecords;
import cn.ffcs.unm.staff.model.LabourContract;
import cn.ffcs.unm.staff.model.PoliticalStatus;
import cn.ffcs.unm.staff.model.RewardSituation;
import cn.ffcs.unm.staff.model.SalaryProjects;
import cn.ffcs.unm.staff.model.SalaryScheme;
import cn.ffcs.unm.staff.model.SalaryTable;
import cn.ffcs.unm.staff.model.SalaryType;
import cn.ffcs.unm.staff.model.SpeSituation;
import cn.ffcs.unm.staff.model.StaffBaseInfo;
import cn.ffcs.unm.staff.model.StaffInfoFile;
import cn.ffcs.unm.staff.model.StaffLanguageAbility;
import cn.ffcs.unm.staff.model.StaffPunishSituation;
import cn.ffcs.unm.staff.model.StaffType;
import cn.ffcs.unm.staff.model.Trial;
import cn.ffcs.uom.common.dao.BaseDaoImpl;
import cn.ffcs.uom.common.util.StrUtil;
import cn.ffcs.uom.common.vo.PageInfo;

@Repository("staffNeimBaseInfoDao")
public class StaffNeimBaseInfoDaoImpl extends BaseDaoImpl implements
		StaffNeimBaseInfoDao {

	@Override
	public PageInfo queryPageInfoByStaffBaseInfo(StaffBaseInfo staffBaseInfo,
			int currentPage, int pageSize) {
		StringBuilder sb = new StringBuilder();
		sb.append(" select * from vie_BD_PSNDOC v where 1=1  ");
		List<Object> params = new ArrayList<Object>();
		if (null != staffBaseInfo) {
			if (!StrUtil.isEmpty(staffBaseInfo.getMonthId())) {
				sb.append(" AND v.monthId = ? ");
				params.add(staffBaseInfo.getMonthId());
			}
			if (!StrUtil.isEmpty(staffBaseInfo.getName())) {
				sb.append(" AND v.NAME LIKE ? ");
				params.add("%"
						+ StringEscapeUtils.escapeSql(staffBaseInfo.getName())
						+ "%");
			}
			if (!StrUtil.isEmpty(staffBaseInfo.getCode())) {
				sb.append(" AND v.CODE LIKE ? ");
				params.add("%"
						+ StringEscapeUtils.escapeSql(staffBaseInfo.getCode())
						+ "%");
			}
		}
		return this.jdbcFindPageInfo(sb.toString(), params, currentPage,
				pageSize, StaffBaseInfo.class);
	}

	@Override
	public PageInfo queryPageInfoByLabourContract(
			LabourContract labourContract, int currentPage, int pageSize) {
		StringBuilder sb = new StringBuilder();
		sb.append(" select * from vie_PSNDOC_CTRT v where 1=1 ");
		List<Object> params = new ArrayList<Object>();
		if (null != labourContract) {
			if (!StrUtil.isEmpty(labourContract.getMonthId())) {
				sb.append(" AND v.monthId = ? ");
				params.add(labourContract.getMonthId());
			}
			if (!StrUtil.isEmpty(labourContract.getName())) {
				sb.append(" AND v.name LIKE ? ");
				params.add("%"
						+ StringEscapeUtils.escapeSql(labourContract.getName())
						+ "%");
			}
		}
		return this.jdbcFindPageInfo(sb.toString(), params, currentPage,
				pageSize, LabourContract.class);
	}

	@Override
	public PageInfo queryPageInfoByRewardSituation(
			RewardSituation rewardSituation, int currentPage, int pageSize) {
		StringBuilder sb = new StringBuilder();
		sb.append(" select * from vie_SNDOC_ENC v where 1=1 ");
		List<Object> params = new ArrayList<Object>();
		if (rewardSituation != null) {
			if (!StrUtil.isEmpty(rewardSituation.getMonthId())) {
				sb.append(" AND v.monthId = ? ");
				params.add(rewardSituation.getMonthId());
			}
			if (!StrUtil.isEmpty(rewardSituation.getName())) {
				sb.append(" AND v.NAME LIKE ? ");
				params.add("%"
						+ StringEscapeUtils.escapeSql(rewardSituation.getName())
						+ "%");
			}
		}
		return this.jdbcFindPageInfo(sb.toString(), params, currentPage,
				pageSize, RewardSituation.class);
	}

	@Override
	public PageInfo queryPageInfoByStaffInfoFile(StaffInfoFile staffInfoFile,
			int currentPage, int pageSize) {
		StringBuilder sb = new StringBuilder();
		sb.append(" select * from vie_PSNDOC_GLBDEF3 v where 1=1 ");
		List<Object> params = new ArrayList<Object>();
		if (staffInfoFile != null) {
			if (!StrUtil.isEmpty(staffInfoFile.getMonthId())) {
				sb.append(" AND v.monthId = ? ");
				params.add(staffInfoFile.getMonthId());
			}
			if (!StrUtil.isEmpty(staffInfoFile.getName())) {
				sb.append(" AND v.NAME LIKE ? ");
				params.add("%"
						+ StringEscapeUtils.escapeSql(staffInfoFile.getName())
						+ "%");
			}
		}
		return this.jdbcFindPageInfo(sb.toString(), params, currentPage,
				pageSize, StaffInfoFile.class);
	}

	@Override
	public PageInfo queryPageInfoBySpeSituation(SpeSituation speSituation,
			int currentPage, int pageSize) {
		StringBuilder sb = new StringBuilder();
		sb.append("select  distinct ja3.name  staffName,ja3.code code, ");
		sb.append(" ja1.MONTH_ID  monthId,ja1.UNIT_ID  单位编码,ja1.PK_PSNDOC_SUB  pkPsndocSub,ja1.CREATIONTIME  创建时间, ");
		sb.append(" ja1.ENDDATE  endDate,ja1.GLBDEF2  时间,ja1.RECORDNUM  recordNum,ja1.LASTFLAG  最近记录标志, ");
		sb.append(" ja1.MODIFIEDTIME  修改时间,ja1.BEGINDATE  beginDate,ja1.TS  时间戳, ");
		sb.append(" ja1.DR	删除标示	,'未翻译====>>>',ja1.GLBDEF1  glbdef1,ja1.GLBDEF4 glbdef4,ja1.GLBDEF3  glbdef3 ");
		sb.append(" from LDAPM_MPW_HI_PSNDOC_GLBDEF2 ja1 ,LDAPM_MPW_bd_psndoc ja3");
		sb.append(" where JA1.pk_psndoc=JA3.pk_psndoc and ja1.MONTH_ID=ja3.MONTH_ID");
		List<Object> params = new ArrayList<Object>();
		if (speSituation != null) {
			if (!StrUtil.isEmpty(speSituation.getMonthId())) {
				sb.append(" AND ja1.MONTH_ID = ? ");
				params.add(speSituation.getMonthId());
			}
			if (!StrUtil.isEmpty(speSituation.getStaffName())) {
				sb.append(" AND ja3.NAME LIKE ? ");
				params.add("%"
						+ StringEscapeUtils.escapeSql(speSituation
								.getStaffName()) + "%");
			}
			if (!StrUtil.isEmpty(speSituation.getCode())) {
				sb.append(" AND ja3.CODE LIKE ? ");
				params.add("%"
						+ StringEscapeUtils.escapeSql(speSituation.getCode())
						+ "%");
			}
		}
		return this.jdbcFindPageInfo(sb.toString(), params, currentPage,
				pageSize, SpeSituation.class);
	}

	@Override
	public PageInfo queryPageInfoByPoliticalStatus(
			PoliticalStatus politicalStatus, int currentPage, int pageSize) {
		StringBuilder sb = new StringBuilder();
		sb.append(" select * from vie_PSNDOC_PARTYLOG v where 1=1 ");
		List<Object> params = new ArrayList<Object>();
		if (politicalStatus != null) {
			if (!StrUtil.isEmpty(politicalStatus.getName())) {
				sb.append(" AND v.NAME LIKE ? ");
				params.add("%"
						+ StringEscapeUtils.escapeSql(politicalStatus.getName())
						+ "%");
			}
			if (!StrUtil.isEmpty(politicalStatus.getMonthId())) {
				sb.append(" AND v.monthId = ? ");
				params.add(politicalStatus.getMonthId());
			}
		}
		return this.jdbcFindPageInfo(sb.toString(), params, currentPage,
				pageSize, PoliticalStatus.class);
	}

	@Override
	public PageInfo queryPageInfoByFormerSituation(
			FormerSituation formerSituation, int currentPage, int pageSize) {
		StringBuilder sb = new StringBuilder();
		sb.append(" select * from vie_PSNDOC_RETIRE v where 1=1 ");
		List<Object> params = new ArrayList<Object>();
		if (formerSituation != null) {
			if (!StrUtil.isEmpty(formerSituation.getMonthId())) {
				sb.append(" AND v.monthId = ? ");
				params.add(formerSituation.getMonthId());
			}
			if (!StrUtil.isEmpty(formerSituation.getName())) {
				sb.append(" AND v.NAME LIKE ? ");
				params.add("%"
						+ StringEscapeUtils.escapeSql(formerSituation.getName())
						+ "%");
			}
		}
		return this.jdbcFindPageInfo(sb.toString(), params, currentPage,
				pageSize, FormerSituation.class);
	}

	@Override
	public PageInfo queryPageInfoByHistoryRecords(
			HistoryRecords historyRecords, int currentPage, int pageSize) {
		StringBuilder sb = new StringBuilder();
		sb.append(" select * from vie_PSNDOC_WORK v where 1=1 ");
		List<Object> params = new ArrayList<Object>();
		if (historyRecords != null) {
			if (!StrUtil.isEmpty(historyRecords.getMonthId())) {
				sb.append(" AND v.monthId = ? ");
				params.add(historyRecords.getMonthId());
			}
			if (!StrUtil.isEmpty(historyRecords.getName())) {
				sb.append(" AND t2.NAME LIKE ? ");
				params.add("%"
						+ StringEscapeUtils.escapeSql(historyRecords.getName())
						+ "%");
			}
		}
		return this.jdbcFindPageInfo(sb.toString(), params, currentPage,
				pageSize, HistoryRecords.class);
	}

	@Override
	public PageInfo queryPageInfoByStaffType(StaffType staffType,
			int currentPage, int pageSize) {
		StringBuilder sb = new StringBuilder();
		sb.append(" select * from vie_BD_PSNCL v where 1=1  ");
		List<Object> params = new ArrayList<Object>();
		if (staffType != null) {
			if (!StrUtil.isEmpty(staffType.getMonthId())) {
				sb.append(" AND v.monthId = ? ");
				params.add(staffType.getMonthId());
			}
			if (!StrUtil.isEmpty(staffType.getName())) {
				sb.append(" AND v.NAME LIKE ? ");
				params.add("%"
						+ StringEscapeUtils.escapeSql(staffType.getName())
						+ "%");
			}
			if (!StrUtil.isEmpty(staffType.getCode())) {
				sb.append(" AND v.CODE LIKE ? ");
				params.add("%"
						+ StringEscapeUtils.escapeSql(staffType.getCode())
						+ "%");
			}
		}
		return this.jdbcFindPageInfo(sb.toString(), params, currentPage,
				pageSize, StaffType.class);
	}

	@Override
	public PageInfo queryPageInfoByTrial(Trial trial, int currentPage, int pageSize) {
		StringBuilder sb = new StringBuilder();
		sb.append(" select * from vie_PSNDOC_TRIAL v where 1=1  ");
		List<Object> params = new ArrayList<Object>();
		if (trial != null) {
			if (!StrUtil.isEmpty(trial.getMonthId())) {
				sb.append(" AND v.monthId = ? ");
				params.add(trial.getMonthId());
			}
			if (!StrUtil.isEmpty(trial.getStaffName())) {
				sb.append(" AND v.name LIKE ? ");
				params.add("%"
						+ StringEscapeUtils.escapeSql(trial
								.getStaffName()) + "%");
			}
		}
		return this.jdbcFindPageInfo(sb.toString(), params, currentPage,
				pageSize, Trial.class);
	}

	@Override
	public PageInfo queryPageInfoByCustomFile(CustomFile customFile,
			int currentPage, int pageSize) {
		StringBuilder sb = new StringBuilder();
		sb.append(" select distinct ja1.MONTH_ID monthId,ja1.PK_DEFDOCLIST  pkDefdoclist,ja1.CODE code,ja1.NAME name, ");
		sb.append(" ja2.name orgName,ja1.DOCCLASS docclass,ja1.DOCTYPE docType,ja1.ISGRADE isGrade, ");
		sb.append(" ja1.CODERULE codeRule,ja1.CODECTLGRADE   codeCtlGrade,ja1.MNGCTLMODE mngctlMode, ");
		sb.append(" ja1.ISRELEASE isRelease,ja1.DOCLEVEL docLevel,ja1.ASSOCIATENAME  associateName, ");
		sb.append(" ja1.CREATIONTIME creationTime,ja1.MODIFIEDTIME modifiedTime,ja1.FUNCODE funCode, ");
		sb.append(" ja1.COMPONENTID componentId,ja1.BPFCOMPONENTID bpfcomponentId,ja1.TS ts,ja1.DR dr ");
		sb.append(" from LDAPM_MPW_BD_DEFDOCLIST ja1 left join LDAPM_MPW_ORG_ORGS ja2  ");
		sb.append(" on (JA1.PK_ORG = JA2.PK_ORG and ja1.MONTH_ID=ja2.MONTH_ID) where 1=1 ");
		List<Object> params = new ArrayList<Object>();
		if (customFile != null) {
			if (!StrUtil.isEmpty(customFile.getMonthId())) {
				sb.append(" AND ja1.MONTH_ID = ? ");
				params.add(customFile.getMonthId());
			}
			if (!StrUtil.isEmpty(customFile.getName())) {
				sb.append(" AND ja1.NAME LIKE ? ");
				params.add("%"
						+ StringEscapeUtils.escapeSql(customFile.getName())
						+ "%");
			}
			if (!StrUtil.isEmpty(customFile.getCode())) {
				sb.append(" AND ja1.CODE LIKE ? ");
				params.add("%"
						+ StringEscapeUtils.escapeSql(customFile.getCode())
						+ "%");
			}
		}
		return this.jdbcFindPageInfo(sb.toString(), params, currentPage,
				pageSize, CustomFile.class);
	}

	@Override
	public PageInfo queryPageInfoBySalaryProjects(
			SalaryProjects salaryProjects, int currentPage, int pageSize) {
		StringBuilder sb = new StringBuilder();
		sb.append(" select * from  vie_WA_ITEM v where 1=1 ");
		List<Object> params = new ArrayList<Object>();
		if (salaryProjects != null) {
			if (!StrUtil.isEmpty(salaryProjects.getMonthId())) {
				sb.append(" AND v.monthId = ? ");
				params.add(salaryProjects.getMonthId());
			}
			if (!StrUtil.isEmpty(salaryProjects.getName())) {
				sb.append(" AND v.NAME LIKE ? ");
				params.add("%"
						+ StringEscapeUtils.escapeSql(salaryProjects.getName())
						+ "%");
			}
			if (!StrUtil.isEmpty(salaryProjects.getCode())) {
				sb.append(" AND v.CODE LIKE ? ");
				params.add("%"
						+ StringEscapeUtils.escapeSql(salaryProjects.getCode())
						+ "%");
			}
		}
		return this.jdbcFindPageInfo(sb.toString(), params, currentPage,
				pageSize, SalaryProjects.class);
	}

	@Override
	public PageInfo queryPageInfoBySalaryScheme(SalaryScheme salaryScheme,
			int currentPage, int pageSize) {
		StringBuilder sb = new StringBuilder();
		sb.append(" select * from vie_WA_WACLASS v where 1=1 ");
		List<Object> params = new ArrayList<Object>();
		if (salaryScheme != null) {
			if (!StrUtil.isEmpty(salaryScheme.getMonthId())) {
				sb.append(" AND v.monthId = ? ");
				params.add(salaryScheme.getMonthId());
			}
			if (!StrUtil.isEmpty(salaryScheme.getName())) {
				sb.append(" AND v.NAME LIKE ? ");
				params.add("%"
						+ StringEscapeUtils.escapeSql(salaryScheme.getName())
						+ "%");
			}
			if (!StrUtil.isEmpty(salaryScheme.getCode())) {
				sb.append(" AND v.CODE LIKE ? ");
				params.add("%"
						+ StringEscapeUtils.escapeSql(salaryScheme.getCode())
						+ "%");
			}
		}
		return this.jdbcFindPageInfo(sb.toString(), params, currentPage,
				pageSize, SalaryScheme.class);
	}

	@Override
	public PageInfo queryPageInfoBySalaryTable(SalaryTable salaryTable,
			int currentPage, int pageSize) {
		StringBuilder sb = new StringBuilder();
		sb.append(" select distinct ja1.PK_INCLUDECLASS pkIncludeClass,ja1.MONTH_ID monthId, ");
		sb.append(" ja1.UNIT_ID unitId,ja2.name orgName,ja1.BATCH batch,ja1.CYEAR cYear, ");
		sb.append(" ja1.CPERIOD cPeriod,ja1.MEMO memo,ja1.DR dr,ja1.TS ts,ja1.PK_PARENTCLASS pkParentClass, ");
		sb.append(" ja1.PK_CHILDCLASS pkChildClass from LDAPM_MPW_WA_INLUDECLASS ja1 ");
		sb.append("  left join LDAPM_MPW_ORG_ORGS ja2 on (JA1.PK_ORG = JA2.PK_ORG and ja1.MONTH_ID=ja2.MONTH_ID) where 1=1 ");
		List<Object> params = new ArrayList<Object>();
		if (salaryTable != null) {
			if (!StrUtil.isEmpty(salaryTable.getMonthId())) {
				sb.append(" AND ja1.MONTH_ID = ? ");
				params.add(salaryTable.getMonthId());
			}
			if (!StrUtil.isEmpty(salaryTable.getCYear())) {
				sb.append(" AND ja1.NAME LIKE ? ");
				params.add("%"
						+ StringEscapeUtils.escapeSql(salaryTable.getCYear())
						+ "%");
			}
		}
		return this.jdbcFindPageInfo(sb.toString(), params, currentPage,
				pageSize, SalaryTable.class);
	}

	@Override
	public PageInfo queryPageInfoBySalaryType(SalaryType salaryType,
			int currentPage, int pageSize) {
		StringBuilder sb = new StringBuilder();
		sb.append(" select distinct ja1.MONTH_ID monthId,ja1.UNIT_ID unitId,ja1.PK_WA_UNITCTG pkWaUnitctg, ");
		sb.append(" ja1.IUNITTYPE iunitType,ja1.DR dr,ja1.TS ts,ja1.PK_WA_CLASS pkWaClass,ja1.CLASSEDID classEdId ");
		sb.append(" from LDAPM_MPW_WA_UNITCTG ja1 where 1=1 ");
		List<Object> params = new ArrayList<Object>();
		if (salaryType != null) {
			if (!StrUtil.isEmpty(salaryType.getMonthId())) {
				sb.append(" AND ja1.MONTH_ID = ? ");
				params.add(salaryType.getMonthId());
			}
			if (!StrUtil.isEmpty(salaryType.getPkWaUnitctg())) {
				sb.append(" AND ja1.PK_WA_UNITCTG LIKE ? ");
				params.add("%"
						+ StringEscapeUtils.escapeSql(salaryType.getPkWaUnitctg())
						+ "%");
			}
		}
		return this.jdbcFindPageInfo(sb.toString(), params, currentPage,
				pageSize, SalaryType.class);
	}

	@Override
	public PageInfo queryPageInfoByStaffLanguageAbility(
			StaffLanguageAbility staffLanguageAbility, int currentPage,
			int pageSize) {
		StringBuilder sb = new StringBuilder();
		sb.append(" select t.MONTH_ID,t.pk_psndoc_sub pkPsndocSub,t1.name name,t1.code code, ");
		sb.append(" t.creationtime creationTime,t.langlev langLev,decode(t.LASTFLAG, 'Y', '是', 'N', '否') lastFlag, ");
		sb.append(" DECODE(t.langsort, '1001A1100000000006C3', '英语', '未知') langSort, ");
		sb.append(" decode(t.langskill,'1001A1100000000006CF','一般','1001A1100000000006CG','良好', ");
		sb.append(" '1001A1100000000006CH','熟练') langSkill from LDAPM_MPW_HI_PSNDOC_LANGABILI t ");
		sb.append(" left join LDAPM_MPW_BD_PSNDOC t1 on (t.pk_psndoc = t1.pk_psndoc and t.MONTH_ID=t1.MONTH_ID) where 1=1 ");
		List<Object> params = new ArrayList<Object>();
		if (staffLanguageAbility != null) {
			if (!StrUtil.isEmpty(staffLanguageAbility.getMonthId())) {
				sb.append(" AND t.MONTH_ID = ? ");
				params.add(staffLanguageAbility.getMonthId());
			}
			if (!StrUtil.isEmpty(staffLanguageAbility.getName())) {
				sb.append(" AND t1.NAME LIKE ? ");
				params.add("%"
						+ StringEscapeUtils.escapeSql(staffLanguageAbility.getName())
						+ "%");
			}
			if (!StrUtil.isEmpty(staffLanguageAbility.getCode())) {
				sb.append(" AND t1.CODE LIKE ? ");
				params.add("%"
						+ StringEscapeUtils.escapeSql(staffLanguageAbility.getCode())
						+ "%");
			}
		}
		return this.jdbcFindPageInfo(sb.toString(), params, currentPage,
				pageSize, StaffLanguageAbility.class);
	}

	@Override
	public PageInfo queryPageInfoByStaffPunishSituation(
			StaffPunishSituation staffPunishSituation, int currentPage,
			int pageSize) {
		StringBuilder sb = new StringBuilder();
		sb.append(" select * from vie_PSNDOC_PUN v where 1=1 ");
		List<Object> params = new ArrayList<Object>();
		if (staffPunishSituation != null) {
			if (!StrUtil.isEmpty(staffPunishSituation.getMonthId())) {
				sb.append(" AND v.monthId = ? ");
				params.add(staffPunishSituation.getMonthId());
			}
			if (!StrUtil.isEmpty(staffPunishSituation.getName())) {
				sb.append(" AND v.NAME LIKE ? ");
				params.add("%"
						+ StringEscapeUtils.escapeSql(staffPunishSituation.getName())
						+ "%");
			}
		}
		return this.jdbcFindPageInfo(sb.toString(), params, currentPage,
				pageSize, StaffPunishSituation.class);
	}

}
